package com.yzzrjy.Dao.Impl;

import com.alibaba.fastjson.JSONObject;
import com.yzzrjy.Dao.Interfaces.UserDao;
import com.yzzrjy.bean.*;
import com.yzzrjy.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Set;

public class UserDaoMsql implements UserDao {

    @Override
    public List<UserShowBean> getOnlineUserList(String myUid, String sex, int start, int pageSize) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select a.*, (select city from login_log where uid=a.uid order by id desc limit 1) AS city " +
                "from user_info a where sex=? order by id limit ?,?";
        List<UserShowBean> list = queryRunner.query(sql, new BeanListHandler<>(UserShowBean.class), sex, start, pageSize);
        return list;
    }

    @Override
    public List<UserShowBean> getOnlineUserList(String myUid, String uidS) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select a.*, (select city from login_log where uid=a.uid order by id desc limit 1) AS city " +
                "from user_info a where uid in(" + uidS + ")";
        List<UserShowBean> list = queryRunner.query(sql, new BeanListHandler<>(UserShowBean.class));
        return list;
    }

    @Override
    public void registerInfo(String uid, String nickname, String sex, byte state) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        queryRunner.update("update user_info set nickname=?,sex=? where uid=?", nickname, sex, uid);
        queryRunner.update("update user_base set status=? where uid=?", state, uid);
    }

    @Override
    public UserBaseBean getUserBaseByPhone(String phone) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        UserBaseBean baseBean = queryRunner.query("select * from user_base where phone=?", new BeanHandler<>(UserBaseBean.class), phone);
        return baseBean;
    }

    @Override
    public UserBaseBean getUserMe(String uid) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select a.*, b.nickname, b.face, b.sex, b.birthday, b.card_attest, b.auth_attest," +
                "(select count(*) from follow_list where auth_uid=?) AS fans," +
                "(select count(*) from follow_list where active_uid=?) AS follow" +
                " from user_base a, user_info b where a.uid=b.uid and a.uid=?";
        UserBaseBean baseBean = queryRunner.query(sql, new BeanHandler<>(UserBaseBean.class), uid, uid, uid);
        return baseBean;
    }

    @Override
    public void addNewUserByPhone(long newUid, String phone, byte state, int coin, String head, String signature) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        queryRunner.update("insert into user_base(uid,phone,status,coin,register_time) values(?,?,?,?,now())",
                newUid, phone, state, coin);
        queryRunner.update("insert into user_info(uid,face,signature) values (?,?,?)",newUid, head, signature);
    }

    @Override
    public EditInfoBean getEditInfo(String uid) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from user_info where uid=?";
        EditInfoBean bean = queryRunner.query(sql, new BeanHandler<>(EditInfoBean.class), uid);
        return bean;
    }

    @Override
    public UserVipBean getUserVipBean(String uid) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        UserVipBean bean = queryRunner.query("select * from user_vip where uid=?", new BeanHandler<>(UserVipBean.class), uid);
        return bean;
    }

    @Override
    public UserLiveBean getUserLiveBean(String liveUid, String myUid) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select a.*, b.consume, b.income, c.city, c.coordinate," +
                "round(st_distance_sphere((select coordinate from login_log where uid=? order by id desc limit 1),coordinate) / 1000,2) AS distance, " +
                "round((select sum(num) from consume_list where send_uid=? and recv_uid=? or send_uid=? and recv_uid=?)/100,2) AS intimacy " +
                "from user_info a left join user_base b on a.uid=b.uid left join login_log c on c.id=(select max(id) from login_log where uid=a.uid) where a.uid=?";
        UserLiveBean bean = queryRunner.query(sql, new BeanHandler<>(UserLiveBean.class), myUid, liveUid, myUid, myUid, liveUid, liveUid);
        return bean;
    }

    @Override
    public UserLiveBean getUserInfoTable(String uid) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from user_info where uid=?";
        UserLiveBean bean = queryRunner.query(sql, new BeanHandler<>(UserLiveBean.class), uid);
        return bean;
    }

    @Override
    public void updateAlbum(String uid, String albumJson) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        queryRunner.update("update user_info set album=? where uid=?", albumJson, uid);
    }

    @Override
    public void updateFace(String uid, String remoteName) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        queryRunner.update("update user_info set face=? where uid=?", remoteName, uid);
    }

    @Override
    public void updateUserInfo(String uid, JSONObject jsonObject) throws SQLException {
        if(jsonObject == null || jsonObject.size() == 0){
            return;
        }
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Set<String> strings = jsonObject.keySet();
        StringBuilder stringBuilder = new StringBuilder("update user_info set ");
        for (String key : strings) {
            String value = jsonObject.getString(key);
            String temp = key + "='" + value + "',";
            stringBuilder.append(temp);
        }
        String sql = stringBuilder.toString();
        if(sql.endsWith(",")){
            sql = sql.substring(0, sql.length() - 1);
        }
        sql += " where uid='" + uid + "'";
        queryRunner.update(sql);
    }

    @Override
    public int searchNickname(String nickname) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Long count = queryRunner.query("select count(id) from user_info where nickname=?", new ScalarHandler<>(), nickname);
        return count.intValue();
    }

    @Override
    public List<UserLetBean> getUserLetList(String uidList) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select uid,nickname,face from user_info where uid in("+ uidList +")";
        List<UserLetBean> list = queryRunner.query(sql, new BeanListHandler<>(UserLetBean.class));
        return list;
    }

    @Override
    public List<UserVipBean> getUserVipList(String uidS) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from user_vip where uid in(" + uidS + ")";
        List<UserVipBean> list = queryRunner.query(sql, new BeanListHandler<>(UserVipBean.class));
        return list;
    }

    @Override
    public void updateBirthday(String uid, String birthday, boolean cardAttest) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        queryRunner.update("update user_info set birthday=?,card_attest=? where uid=?", birthday, cardAttest, uid);
    }


}
